package com.xky.ui.module.inventorManagement.select;

import com.xky.pojo.Inventory;
import com.xky.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 * 库存查询工具类
 */
public class SelectInvenTory {

    /**
     * 查询所有库存记录
     * @return 库存记录列表
     */
    public static List<Inventory> selectAllInventory() {
        String sql = "SELECT * FROM inventory";
        return executeInventoryQuery(sql, null);
    }

    /**
     * 根据库存ID查询是否存在
     * @param id 库存ID
     * @return 如果存在返回true，否则返回false
     */
    public static boolean isInventoryExistById(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT 1 FROM inventory WHERE inventory_id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            return resultSet.next(); // 只需要检查是否存在记录
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 根据库存ID查询库存记录
     * @param id 库存ID
     * @return 库存记录列表
     */
    public static List<Inventory> selectInventoryById(Integer id) {
        String sql = "SELECT * FROM inventory WHERE inventory_id = ?";
        return executeInventoryQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, id);
        });
    }

    /**
     * 根据库位编码或批次号进行模糊查询
     * @param keyword 关键词
     * @return 库存列表
     */
    public static List<Inventory> searchInventoryByKeyword(String keyword) {
        String sql = "SELECT * FROM inventory WHERE location_code LIKE ? OR batch_number LIKE ?";
        return executeInventoryQuery(sql, preparedStatement -> {
            String searchPattern = "%" + keyword + "%";
            preparedStatement.setString(1, searchPattern);
            preparedStatement.setString(2, searchPattern);
        });
    }

    /**
     * 根据商品ID查询库存
     * @param productId 商品ID
     * @return 库存列表
     */
    public static List<Inventory> searchInventoryByProductId(Integer productId) {
        String sql = "SELECT * FROM inventory WHERE product_id = ?";
        return executeInventoryQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, productId);
        });
    }

    /**
     * 根据仓库ID查询库存
     * @param warehouseId 仓库ID
     * @return 库存列表
     */
    public static List<Inventory> searchInventoryByWarehouseId(Integer warehouseId) {
        String sql = "SELECT * FROM inventory WHERE warehouse_id = ?";
        return executeInventoryQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, warehouseId);
        });
    }

    /**
     * 根据供应商ID查询库存
     * @param supplierId 供应商ID
     * @return 库存列表
     */
    public static List<Inventory> selectInventoryBySupplier(Integer supplierId) {
        String sql = 
            "SELECT i.*, p.product_name, w.warehouse_name " +
            "FROM inventory i " +
            "JOIN products p ON i.product_id = p.product_id " +
            "JOIN warehouses w ON i.warehouse_id = w.warehouse_id " +
            "WHERE i.supplier_id = ? " +
            "ORDER BY i.inventory_id";
            
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Inventory> inventoryList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, supplierId);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Inventory inventory = new Inventory();
                inventory.setInventoryId(resultSet.getInt("inventory_id"));
                inventory.setProductId(resultSet.getInt("product_id"));
                inventory.setWarehouseId(resultSet.getInt("warehouse_id"));
                inventory.setQuantity(resultSet.getInt("quantity"));

                // 获取创建时间和更新时间
                setTimestampFields(resultSet, inventory);

                inventoryList.add(inventory);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return inventoryList;
    }

    /**
     * 执行库存查询并返回结果列表
     * @param sql SQL查询语句
     * @param preparer SQL参数设置器
     * @return 库存记录列表
     */
    private static List<Inventory> executeInventoryQuery(String sql, PreparedStatementPreparer preparer) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Inventory> inventoryList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            
            // 如果有参数需要设置
            if (preparer != null) {
                preparer.prepare(preparedStatement);
            }
            
            resultSet = preparedStatement.executeQuery();
            
            while (resultSet.next()) {
                Inventory inventory = createInventoryFromResultSet(resultSet);
                inventoryList.add(inventory);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 记录错误但不中断程序流程
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return inventoryList;
    }
    
    /**
     * 从ResultSet创建Inventory对象
     * @param resultSet 结果集
     * @return Inventory对象
     * @throws SQLException 如果访问结果集出错
     */
    private static Inventory createInventoryFromResultSet(ResultSet resultSet) throws SQLException {
        Inventory inventory = new Inventory();
        inventory.setInventoryId(resultSet.getInt("inventory_id"));
        inventory.setProductId(resultSet.getInt("product_id"));
        inventory.setWarehouseId(resultSet.getInt("warehouse_id"));
        inventory.setQuantity(resultSet.getInt("quantity"));
        inventory.setAllocatedQuantity(resultSet.getInt("allocated_quantity"));
        inventory.setLocationCode(resultSet.getString("location_code"));
        inventory.setBatchNumber(resultSet.getString("batch_number"));
        
        // 设置时间戳字段
        setTimestampFields(resultSet, inventory);
        
        return inventory;
    }
    
    /**
     * 设置Inventory对象的时间戳字段
     * @param resultSet 结果集
     * @param inventory 库存对象
     * @throws SQLException 如果访问结果集出错
     */
    private static void setTimestampFields(ResultSet resultSet, Inventory inventory) throws SQLException {
        // 设置过期日期
        Timestamp expiryDate = resultSet.getTimestamp("expiry_date");
        if (expiryDate != null) {
            inventory.setExpiryDate(expiryDate.toLocalDateTime());
        }
        
        // 设置创建时间
        Timestamp createdTime = resultSet.getTimestamp("created_time");
        if (createdTime != null) {
            inventory.setCreatedTime(createdTime.toLocalDateTime());
        }
        
        // 设置更新时间
        Timestamp updatedTime = resultSet.getTimestamp("updated_time");
        if (updatedTime != null) {
            inventory.setUpdatedTime(updatedTime.toLocalDateTime());
        }
    }
    
    /**
     * PreparedStatement参数设置器接口
     */
    @FunctionalInterface
    private interface PreparedStatementPreparer {
        void prepare(PreparedStatement preparedStatement) throws SQLException;
    }
}
